﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
using System.Windows;
namespace Convert.DBconverter
{
    class PersonsDebtorsConverter
    {
        private static int BATCH_SAVE_SIZE = 10;    //save 10 accounts at a time - includes all transactions in those accounts too
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;
        private static Dictionary<int, DebtorHeaderDetail> LotsCustomerIDToCPIAMDebtorHeaderClientID;
        public static Dictionary<int, DebtorHeaderDetail> ConvertPersonsDebtors(string lotsConnectionString, string cpiamConnectionString)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            PopulateAccountBalanceDic();
            PopulateDebtorTransDic();
            PopulatePostCodeStateDic();
            LotsCustomerIDToCPIAMDebtorHeaderClientID = new Dictionary<int, DebtorHeaderDetail>();
            DataTable ClientDT = DataTables.CreateClientFileDT();
            DataTable ClubMembersDT = DataTables.CreateClubMembersDT();
            DataTable PatientsDT = DataTables.CreatePatientsFileDT();
            DataTable DebtorHeaderDT = DataTables.CreateDebtorHeaderDT();
            DataTable DebtorTransDT = DataTables.CreateDebtorTransDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT Person.PersonID, Person.FirstName, Person.LastName, Title.Title, Person.Address, Person.Suburb, Person.PostCode, Person.PhoneNumber, Person.BirthDate, Person.MedicareNo, Person.AccountID, Person.CreditLimit, Person.AccountClassID
                        FROM (Person LEFT OUTER JOIN Title ON Person.TitleID = Title.TitleID)
                        WHERE (Person.AccountID <>0) AND (Person.PersonID > 0)");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        PersonDebtor personDebtor = new PersonDebtor();
                        int i = 0;
                        personDebtor.PersonID = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
                        personDebtor.FirstName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                        personDebtor.LastName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                        personDebtor.Title = reader.IsDBNull(++i) ? null : reader.GetString(i);
                        personDebtor.Address = reader.IsDBNull(++i) ? null : reader.GetString(i);
                        personDebtor.Suburb = reader.IsDBNull(++i) ? null : reader.GetString(i);
                        personDebtor.PostCode = reader.IsDBNull(++i) ? null : reader.GetString(i);
                        personDebtor.PhoneNumber = reader.IsDBNull(++i) ? null : reader.GetString(i);
                        personDebtor.BirthDate = reader.IsDBNull(++i) ? DateTime.Now : reader.GetDateTime(i);
                        personDebtor.MedicareNo = reader.IsDBNull(++i) ? null : reader.GetString(i);
                        personDebtor.AccountID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                        personDebtor.CreditLimit = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                        personDebtor.AccountClassID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                        SavePersonDebtor(personDebtor, ClientDT, ClubMembersDT, PatientsDT, DebtorHeaderDT, DebtorTransDT);
                        if (ClientDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        {
                            SaveAllDataTables(CpiamConnectionString, ClientDT, ClubMembersDT, PatientsDT, DebtorHeaderDT, DebtorTransDT);
                        }
                    }
                }
                SaveAllDataTables(CpiamConnectionString, ClientDT, ClubMembersDT, PatientsDT, DebtorHeaderDT, DebtorTransDT); 
            }
            catch (Exception ex)
            {
                Logger.LogWarningAndError("Error in Convert PersonDebtor ", ex);
                MessageBox.Show("Error converting PersonDebtor: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
            }
            finally
            {
                dbConn.Close();
            }
            //UpdateCurBalAndTotBal();
            return LotsCustomerIDToCPIAMDebtorHeaderClientID;
        }

        private static void SaveAllDataTables(String ConnectionString, DataTable ClientDT, DataTable ClubMembersDT, DataTable PatientsDT, DataTable DebtorHeaderDT, DataTable DebtorTransDT)
        {
            if (ClientDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(ClientDT, "ClientsFile", ConnectionString);
            if (ClubMembersDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(ClubMembersDT, "ClubMembersFile", ConnectionString);
            if (PatientsDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(PatientsDT, "PatientsFile", ConnectionString);
            if (DebtorHeaderDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(DebtorHeaderDT, "DebtorHeaderFile", ConnectionString);
            if (DebtorTransDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(DebtorTransDT, "DebtorTransFile", ConnectionString);
        }

        private static void SavePersonDebtor(PersonDebtor personDebtor, DataTable ClientDT, DataTable ClubMembersDT, DataTable PatientsDT, DataTable DebtorHeaderDT, DataTable DebtorTransDT)
        {
            
            Guid ClientID = Guid.Empty;
            int ClientNo = personDebtor.PersonID;
            string ClientSex = "Unknown";
            string ClientPhoneNumber = string.Empty;
            string ClientSalu = string.Empty;
            DateTime ClientBirthDate = DateTime.Now;
            string ClientPostalPCode = "0000";
            string ClientFirstName = string.Empty;
            string ClientPostalState = "AAA";
            string ClientPostalAddress = "Address Not Supplied";
            string ClientPostalSuburb = "None";
            string ClientSearchMarker = string.Empty;
            string ClientIDType = "Not Identified";
            string ClientIDDetails = "A";
            ClientPhoneNumber = personDebtor.PhoneNumber == null ? string.Empty : personDebtor.PhoneNumber;
            if (personDebtor.Title == null)
            {
                ClientSex = "U";
                ClientSalu = String.Empty;
            }
            else
            {
                ClientSalu = personDebtor.Title;
                if (personDebtor.Title.ToUpper() == "MR")
                    ClientSex = "M";
                else if (personDebtor.Title.ToUpper() == "MR.")
                    ClientSex = "M";
                else if (personDebtor.Title.ToUpper() == "FR")
                    ClientSex = "M";
                else if (personDebtor.Title.ToUpper() == "MISS")
                    ClientSex = "F";
                else if (personDebtor.Title.ToUpper() == "MRS")
                    ClientSex = "F";
                else if (personDebtor.Title.ToUpper() == "MS")
                    ClientSex = "F";
                else if (personDebtor.Title.ToUpper() == "MS.")
                    ClientSex = "F";
                else if (personDebtor.Title.ToUpper() == "MASTER")
                    ClientSex = "M";
                else if (personDebtor.Title.ToUpper() == "BR")
                    ClientSex = "M";
                else if (personDebtor.Title.ToUpper() == "SIR")
                    ClientSex = "M";
                else if (personDebtor.Title.ToUpper() == "LADY")
                    ClientSex = "F";
                else if (personDebtor.Title.ToUpper() == "MSTR")
                    ClientSex = "M";
                else
                    ClientSex = "U";
            }

            ClientBirthDate = personDebtor.BirthDate;
            ClientPostalPCode = personDebtor.PostCode == null ? "0000" : personDebtor.PostCode;
            ClientFirstName = personDebtor.FirstName == null ? string.Empty : personDebtor.FirstName.Replace("!", "").Replace("''", "").Replace("#", "").Replace("*", "").Replace("(", "").Replace(".", "").Replace("@", "").Replace("^", "").Replace("_", "").Trim().ToUpper();
            if (PostCodeStateDic.ContainsKey(personDebtor.PostCode))
            {
                ClientPostalState = PostCodeStateDic[personDebtor.PostCode];
            }
            
            /*
            if (personDebtor.PostCode.StartsWith("2"))
                ClientPostalState = "NSW";
            else if (personDebtor.PostCode.StartsWith("4"))
                ClientPostalState = "QLD";
            else if (personDebtor.PostCode.StartsWith("3"))
                ClientPostalState = "VIC";
            else
                ClientPostalState = "AAA";
            */

            ClientPostalAddress = personDebtor.Address == null ? "Address Not Supplied" : personDebtor.Address;
            ClientPostalSuburb = personDebtor.Suburb == null ? "None" : personDebtor.Suburb;
            ClientSearchMarker = personDebtor.LastName.Length >= 2 ? personDebtor.LastName.Trim().Substring(0, 2).ToUpper() : personDebtor.LastName.ToUpper();
            if (personDebtor.MedicareNo == null)
            {
                ClientIDType = "Not Identified";
                ClientIDDetails = string.Empty;
            }
            else
            {
                if (personDebtor.MedicareNo.Length > 0)
                {
                    ClientIDType = "Medicare Number";
                    ClientIDDetails = personDebtor.MedicareNo;
                }
                else
                {
                    ClientIDType = "Not Identified";
                    ClientIDDetails = string.Empty;
                }
            }

            ClientID = Guid.NewGuid();
            Guid debtorHeaderID = Guid.NewGuid();
            DebtorHeaderDetail detail = new DebtorHeaderDetail();
            detail.DebtorHeaderID = debtorHeaderID;
            detail.ClientID = ClientID;
            detail.ClientNo = personDebtor.AccountID;
            LotsCustomerIDToCPIAMDebtorHeaderClientID[personDebtor.PersonID] = detail;

            Client client = new Client(ClientID, ClientNo, "None", 1, DateTime.Now, "System",
                          personDebtor.LastName.Trim().ToUpper(),
                          personDebtor.FirstName.Trim(),ClientSearchMarker,
                          ClientSalu, ClientSex, ClientBirthDate, ClientPostalAddress,
                          ClientPostalSuburb, ClientPostalState, ClientPostalPCode,
                          ClientPostalAddress, ClientPostalSuburb, ClientPostalState, ClientPostalPCode,
                          ClientPhoneNumber, String.Empty, String.Empty,
                          String.Empty, true, DateTime.Now, "A", "General, Debtor",
                          ClientIDType, ClientIDDetails);

            Client.SaveClientToDataTable(client, ClientDT);
            ClubMembers clubMember = new ClubMembers(ClientID, 0, 0, 0, 0, false, false);
            ClubMembers.SaveClubMembersToDataTable(clubMember, ClubMembersDT);
            Patients patient = new Patients(ClientID);
            Patients.SavePatientsToDataTable(patient, PatientsDT);
            decimal decDebtCurrBal = 0;
            decimal decDebtTotalBal = 0;
            decimal decOpenBal = 0;
            decimal dec30DayBal = 0;
            decimal dec60DayBal = 0;
            decimal dec90DayBal = 0;
            //decimal decPayment = 0;

            if (AccountIDtoBalanceDic.ContainsKey(personDebtor.AccountID))
                decDebtTotalBal = AccountIDtoBalanceDic[personDebtor.AccountID] / 100;

            decimal decCreditLimit = personDebtor.CreditLimit / 100;
            string strDebtorGroup = "AA";
            //ConvertDebtorTrans(personDebtor.PersonID, debtorHeader.DebtorHeaderID);
            decimal sumOfTransactions = 0;

            DateTime currentDate  =  DateTime.Now;  

            //DateTime startMonth1  =  dt.AddDays(1 - dt.Day);
            //DateTime endMonth1  =  startMonth1.AddMonths(1).AddDays(-1);

            //DateTime startMonth2 = startMonth1.AddMonths(-1);
            //DateTime endMonth2 = endMonth1.AddMonths(-1);
            //DateTime startMonth3 = startMonth1.AddMonths(-2);
            //DateTime endMonth3 = endMonth1.AddMonths(-2);

            DateTime monthCurrentStart = new DateTime(currentDate.Year, currentDate.Month, 1);
            DateTime month30DayStart = monthCurrentStart.AddMonths(-1);
            DateTime month60DayStart = month30DayStart.AddMonths(-1);

            DateTime earliestDate = DateTime.Now;
            if (DebtorTransDic.ContainsKey(personDebtor.AccountID))
            {
                foreach (PersonDebtorTrans personDebtorTrans in DebtorTransDic[personDebtor.AccountID])
                {
                    if (personDebtorTrans.TimeDate < earliestDate)
                        earliestDate = personDebtorTrans.TimeDate;
                    string strType = "A";
                    Guid guidStkID = Guid.Empty;
                    decimal decAmt = 0;
                    string GSTMarker = personDebtorTrans.GSTPaid >= 0 ? "*" : "";
                    decimal GSTAmt = (decimal)personDebtorTrans.GSTPaid / 100;
                    double discount = personDebtorTrans.Discount / 100;

                    string debtorTransDescription = personDebtorTrans.Description != null ? personDebtorTrans.Description : string.Empty;

                    if (personDebtorTrans.StockID > 0)
                    {
                        guidStkID = GetStockGUIDFromID(personDebtorTrans.StockID);
                        if (debtorTransDescription.ToUpper().StartsWith("RX"))
                            strType = "S";
                        else
                            strType = "I";
                    }
                    else
                    {
                        guidStkID = Guid.Empty;

                        if (debtorTransDescription.ToUpper().Contains("PAYMENT RECEIVED"))
                            strType = "P";
                        else if (debtorTransDescription.ToUpper().Contains("DISCOUNT"))
                            strType = "D";
                        else
                            strType = "A";
                    }

                    if (strType == "P")
                    {
                        decAmt = (decimal)Math.Abs(personDebtorTrans.Retail / 100) * -1;
                        //decPayment += decAmt;
                    }
                    else
                    {
                        if ((int)personDebtorTrans.Discount == 0)
                        {
                            decAmt = (decimal)(personDebtorTrans.Retail * personDebtorTrans.Quantity / 100);
                        }
                        else
                        {
                            decAmt = (decimal)(personDebtorTrans.Retail * (1 - discount) * personDebtorTrans.Quantity / 100);
                            decAmt = Math.Round(decAmt, 2, MidpointRounding.AwayFromZero);
                        }

                        //if (personDebtorTrans.TimeDate >= startMonth1 && personDebtorTrans.TimeDate <= endMonth1)
                        //{
                        //    decDebtCurrBal += decAmt;
                        //}
                        //else if (personDebtorTrans.TimeDate >= startMonth2 && personDebtorTrans.TimeDate <= endMonth2)
                        //{
                        //    dec30DayBal += decAmt;
                        //}
                        //else if (personDebtorTrans.TimeDate >= startMonth3 && personDebtorTrans.TimeDate <= endMonth3)
                        //{
                        //    dec60DayBal += decAmt;
                        //}
                        //else if (personDebtorTrans.TimeDate < startMonth3)
                        //{
                        //    dec90DayBal += decAmt;
                        //}
                    }

                    //14/04/2013: changed the way in which 30/60/90 day balances are calculated (any charge/payment made in a period will only adjust the balance in that period
                    if (personDebtorTrans.TimeDate >= monthCurrentStart)
                        decDebtCurrBal += decAmt;
                    else if (personDebtorTrans.TimeDate >= month30DayStart)
                        dec30DayBal += decAmt;
                    else if (personDebtorTrans.TimeDate >= month60DayStart)
                        dec60DayBal += decAmt;
                    else
                        dec90DayBal += decAmt;

                    DebtorTransFile debtorTransFile = new DebtorTransFile(debtorHeaderID, personDebtorTrans.TimeDate, guidStkID, debtorTransDescription.ToUpper(), GSTMarker, personDebtorTrans.Quantity, strType, decAmt, GSTAmt, personDebtorTrans.SaleID);
                    DebtorTransFile.SaveDebtorTransFileToDataTable(debtorTransFile, DebtorTransDT);
                    sumOfTransactions += decAmt;
                }
            }


            //decPayment = decPayment * -1;
            //if (decPayment <= dec90DayBal)
            //{
            //    dec90DayBal = dec90DayBal - decPayment;
            //}
            //else if (decPayment <= dec90DayBal + dec60DayBal)
            //{
            //    dec90DayBal = 0;
            //    dec60DayBal = dec90DayBal + dec60DayBal - decPayment;
            //}
            //else if (decPayment <= dec90DayBal + dec60DayBal + dec30DayBal)
            //{
            //    dec90DayBal = 0;
            //    dec60DayBal = 0;
            //    dec30DayBal = dec90DayBal + dec60DayBal + dec30DayBal - decPayment;
            //}
            //else if (decPayment <= dec90DayBal + dec60DayBal + dec30DayBal + decDebtCurrBal)
            //{
            //    dec90DayBal = 0;
            //    dec60DayBal = 0;
            //    dec30DayBal = 0;
            //    decDebtCurrBal = dec90DayBal + dec60DayBal + dec30DayBal + decDebtCurrBal - decPayment;
            //}
            //else
            //{
            //    dec90DayBal = 0;
            //    dec60DayBal = 0;
            //    dec30DayBal = 0;
            //    decDebtCurrBal = 0;
            //}

            decimal amountUnbalanced = Decimal.Round(decDebtTotalBal - sumOfTransactions, 2, MidpointRounding.AwayFromZero);
            if (amountUnbalanced != 0)
            {
                DebtorTransFile debtorTransFile = new DebtorTransFile(debtorHeaderID, earliestDate, Guid.Empty, "Manual Adjustment from Conversion", String.Empty, 1, "A", amountUnbalanced, 0, 0);
                DebtorTransFile.SaveDebtorTransFileToDataTable(debtorTransFile, DebtorTransDT);

                if (earliestDate >= monthCurrentStart)
                    decDebtCurrBal += amountUnbalanced;
                else if (earliestDate >= month30DayStart)
                    dec30DayBal += amountUnbalanced;
                else if (earliestDate >= month60DayStart)
                    dec60DayBal += amountUnbalanced;
                else
                    dec90DayBal += amountUnbalanced;
            }
            DebtorHeader debtorHeader = new DebtorHeader(debtorHeaderID, ClientID, decDebtCurrBal, decDebtTotalBal, decOpenBal, dec30DayBal, dec60DayBal, dec90DayBal, decCreditLimit, strDebtorGroup, personDebtor.AccountID);
            DebtorHeader.SaveDebtorHeaderDataTable(debtorHeader, DebtorHeaderDT);
        }

        /*
        public static void ConvertDebtorTrans(int PersonID, Guid DebtorHeaderID)
        {
            DataTable DebtorTransDT = DataTables.CreateDebtorTransDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT SaleItem.SaleID, Sales.TimeDate, SaleItem.StockID, SaleItem.Description, SaleItem.Retail, SaleItem.Quantity, SaleItem.GSTPaid, SaleItem.PersonID, salepayment.accountID
                    FROM SaleItem, sales, salepayment 
                    WHERE SaleItem.PersonID = @ID and SaleItem.SaleID = Sales.SaleID and salepayment.saleID=sales.saleid and salepayment.accountID<>0 ORDER BY Sales.TimeDate");
                    command.Parameters.AddWithValue("@ID", PersonID);
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        try
                        {
                            PersonDebtorTrans personDebtorTrans = new PersonDebtorTrans();
                            int i = 0;
                            personDebtorTrans.SaleID = reader.GetInt32(i);
                            personDebtorTrans.TimeDate = reader.GetDateTime(++i);
                            personDebtorTrans.StockID = reader.GetInt32(++i);
                            personDebtorTrans.Description = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personDebtorTrans.Retail = reader.GetInt32(++i);
                            personDebtorTrans.Quantity = reader.IsDBNull(++i) ? 0 : reader.GetFloat(i);
                            personDebtorTrans.GSTPaid = reader.GetInt32(++i);
                            personDebtorTrans.PersonID = reader.GetInt32(++i);
                            SavePersonDebtorTrans(personDebtorTrans, DebtorTransDT, DebtorHeaderID);
                        }
                        catch (Exception ex)
                        {
                            Logger.LogWarningAndError("Error in Convert DebtorTrans ", ex);
                        }

                        if (DebtorTransDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        {
                            SaveDebtorTransDataTables(CpiamConnectionString, DebtorTransDT);
                        }
                    }
                    SaveDebtorTransDataTables(CpiamConnectionString, DebtorTransDT);
                }

                dbConn.Close();

        }

        private static void SaveDebtorTransDataTables(String ConnectionString, DataTable DebtorTransDT)
        {
            if (DebtorTransDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(DebtorTransDT, "DebtorTransFile", ConnectionString);

        }

        private static void SavePersonDebtorTrans(PersonDebtorTrans personDebtorTrans, DataTable DebtorTransDT, Guid DebtorHeaderID)
        {
            string strType="A";
            Guid guidStkID = Guid.Empty;
            decimal decAmt=0;
            string GSTMarker = personDebtorTrans.GSTPaid>=0?"*":"";
            decimal GSTAmt = (decimal) personDebtorTrans.GSTPaid / 100;
            if (personDebtorTrans.Description != null)
            {
                if (personDebtorTrans.StockID > 0)
                {
                    guidStkID = GetStockGUIDFromID(personDebtorTrans.StockID);
                    if (personDebtorTrans.Description.ToUpper().StartsWith("RX"))
                        strType = "S";
                    else
                        strType = "I";
                }
                else
                {
                    guidStkID = Guid.Empty;
                    
                    if (personDebtorTrans.Description.ToUpper().Contains("PAYMENT RECEIVED"))
                        strType = "P";
                    else if (personDebtorTrans.Description.ToUpper().Contains("DISCOUNT"))
                        strType = "D";
                    else
                        strType = "A";
                }
                if (strType == "P")
                    decAmt = (decimal) Math.Abs(personDebtorTrans.Retail / 100) * -1;
                else
                    decAmt = (decimal) (personDebtorTrans.Retail * personDebtorTrans.Quantity / 100);

            }

            DebtorTransFile debtorTransFile = new DebtorTransFile(DebtorHeaderID, personDebtorTrans.TimeDate,guidStkID,personDebtorTrans.Description.ToUpper(),GSTMarker,personDebtorTrans.Quantity,strType,decAmt,GSTAmt,personDebtorTrans.SaleID);
            DebtorTransFile.SaveDebtorTransFileToDataTable(debtorTransFile, DebtorTransDT);
        }
        */

        public static Dictionary<int, List<PersonDebtorTrans>> DebtorTransDic;
        public static void PopulateDebtorTransDic()
        {
            DebtorTransDic = new Dictionary<int, List<PersonDebtorTrans>>();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            dbConn.Open();
            using (OleDbCommand command = dbConn.CreateCommand())
            {
                //will only get account charges
                command.CommandText = string.Format(@"SELECT SaleItem.SaleID, Sales.TimeDate, SaleItem.StockID, SaleItem.Description, SaleItem.Retail, SaleItem.Quantity, SaleItem.GSTPaid, SalePayment.accountID, saleItem.discount
                    FROM SaleItem, sales, salepayment 
                    WHERE SaleItem.SaleID = Sales.SaleID and salepayment.saleID=sales.saleid and salepayment.accountID<>0 and Type = 6 ORDER BY SalePayment.accountID");
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    PersonDebtorTrans personDebtorTrans = new PersonDebtorTrans();
                    int i = 0;
                    personDebtorTrans.SaleID = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.TimeDate = reader.IsDBNull(++i) ? DateTime.Now : reader.GetDateTime(i);
                    personDebtorTrans.StockID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.Description = reader.IsDBNull(++i) ? null : reader.GetString(i);
                    personDebtorTrans.Retail = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.Quantity = reader.IsDBNull(++i) ? 0 : reader.GetFloat(i);
                    personDebtorTrans.GSTPaid = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.AccountID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.Discount = reader.IsDBNull(++i) ? 0 : reader.GetFloat(i);
                    if (!DebtorTransDic.ContainsKey(personDebtorTrans.AccountID))
                        DebtorTransDic[personDebtorTrans.AccountID] = new List<PersonDebtorTrans>();
                    DebtorTransDic[personDebtorTrans.AccountID].Add(personDebtorTrans);
                }
                reader.Close();
                //will only get account payments
                command.CommandText = string.Format(@"SELECT salepayment.saleid, Sales.TimeDate, 0, 'PAYMENT RECEIVED', SUM(salepayment.value), 1, 0, salepayment.AccountID, 0
                FROM sales, salepayment 
                WHERE salepayment.saleID=sales.saleid and salepayment.accountID<>0 and Type <> 6 
                GROUP BY salepayment.saleid, salepayment.AccountID, Sales.TimeDate");
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    PersonDebtorTrans personDebtorTrans = new PersonDebtorTrans();
                    int i = 0;
                    personDebtorTrans.SaleID = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.TimeDate = reader.IsDBNull(++i) ? DateTime.Now : reader.GetDateTime(i);
                    personDebtorTrans.StockID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.Description = reader.IsDBNull(++i) ? null : reader.GetString(i);
                    personDebtorTrans.Retail = reader.IsDBNull(++i) ? 0 : reader.GetDouble(i);
                    personDebtorTrans.Quantity = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.GSTPaid = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.AccountID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    personDebtorTrans.Discount = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                    if (!DebtorTransDic.ContainsKey(personDebtorTrans.AccountID))
                        DebtorTransDic[personDebtorTrans.AccountID] = new List<PersonDebtorTrans>();
                    DebtorTransDic[personDebtorTrans.AccountID].Add(personDebtorTrans);
                }
                reader.Close();
                dbConn.Close();
            }
        }

        public static Dictionary<string, string> PostCodeStateDic;
        public static void PopulatePostCodeStateDic()
        {
            PostCodeStateDic = new Dictionary<string, string>();
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            dbConn.Open();
            using (SqlCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"select fldPCode, fldState from tblSuburbs");
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    string postcode = reader.GetString(0);
                    string State = reader.IsDBNull(1) ? null : reader.GetString(1);
                    PostCodeStateDic[postcode] = State;
                }
                reader.Close();
            }
            dbConn.Close();
        }

        private static Guid GetStockGUIDFromID(int StockID)
        {
            Guid StockGUID = Guid.Empty;
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"Select StkID from StockFile where StkNumber = @StockID");
            command.Parameters.AddWithValue("@StockID", StockID);
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                StockGUID = reader.GetGuid(0);
            }
            dbConn.Close();
            return StockGUID;
        }

        private static void UpdateCurBalAndTotBal()
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE A SET [DebtCurrBal] = B.[TotBalance], [DebtTotalBal] = B.[TotBalance]
            FROM [dbo].[DebtorHeaderFile] A INNER JOIN (SELECT [DebtHeaderID], SUM([DebtTransAmt]) AS [TotBalance] FROM [dbo].[DebtorTransFile]
            GROUP BY [DebtHeaderID]) B ON A.[DebtHeaderID] = B.[DebtHeaderID]");
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        public static Dictionary<int, decimal> AccountIDtoBalanceMap;
        public static void PopulateAccountBalance()
        {
            AccountIDtoBalanceMap = new Dictionary<int, decimal>();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            dbConn.Open();
            using (OleDbCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"SELECT a.accountID,a.FrozenBalance from salepayment a where a.accountID<>0 and type<>0  and saleID= (SELECT max(b.saleID) from salePayment b where b.accountID=a.accountID)  order by a.accountID");
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int accountID = reader.GetInt32(0);
                    decimal balance = reader.IsDBNull(1) ? 0 : reader.GetInt32(1);
                    AccountIDtoBalanceMap[accountID] = balance;
                }
                reader.Close();
            }
            dbConn.Close();
        }

        public static Dictionary<int, decimal> AccountIDtoBalanceDic;
        public static void PopulateAccountBalanceDic()
        {
            AccountIDtoBalanceDic = new Dictionary<int, decimal>();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            dbConn.Open();
            using (OleDbCommand command = dbConn.CreateCommand())
            {
                command.CommandText = string.Format(@"SELECT statementid, closingdate, accountid, closingbalance from statement a where a.statementid = (select max(statementID) from statement b where b.accountid=a.accountid ) order by accountid, statementid");
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    int statementID = reader.GetInt32(0);
                    DateTime closingDate = reader.GetDateTime(1);
                    int accountID = reader.GetInt32(2);
                    decimal balance = reader.IsDBNull(3) ? 0 : reader.GetInt32(3);

                    OleDbConnection dbConns = new OleDbConnection(LotsConnectionString);
                    dbConns.Open();
                    using (OleDbCommand commands = dbConn.CreateCommand())
                    {
                        //14/04/2013 renv: Need to take into account discounts for total balance
                        commands.CommandText = string.Format(@"SELECT SUM(SaleItem.Retail * SaleItem.Quantity* (1 - SaleItem.Discount/100))
                        FROM SaleItem, sales, salepayment 
                        WHERE SaleItem.SaleID = Sales.SaleID and salepayment.saleID=sales.saleid and salepayment.accountID=@accountID and Type = 6 and sales.TimeDate>@timeDate group by salepayment.accountID");
                        commands.Parameters.AddWithValue("@accountID", accountID);
                        commands.Parameters.AddWithValue("@timeDate", closingDate);
                        OleDbDataReader readers = commands.ExecuteReader();
                        while (readers.Read())
                        {
                            try
                            {
                                double amt = readers.GetDouble(0);
                                balance += (decimal)amt;
                            }
                            catch (Exception ex)
                            {
                                object o = readers.GetValue(0);
                                Type t = o != null ? o.GetType() : null;
                                MessageBox.Show("PersonsDebtorsConverter: ex message=" + ex.Message + " stacktrace=" + ex.StackTrace + "\n\n. Object=" + o + " type=" + t);
                            }
                        }
                        readers.Close();

                        commands.CommandText = string.Format(@"SELECT value from salepayment,sales where accountID=@accountID and type <>6 and sales.saleid=salepayment.saleid and sales.TimeDate>@timeDate");
                        commands.Parameters.AddWithValue("@accountID", accountID);
                        commands.Parameters.AddWithValue("@timeDate", closingDate);
                        readers = commands.ExecuteReader();
                        while (readers.Read())
                        {
                            double amt = readers.GetInt32(0);
                            balance -= (decimal)amt;
                        }
                        readers.Close();

                    }
                    dbConns.Close();

                    AccountIDtoBalanceDic[accountID] = balance;
                }
                reader.Close();
            }
            dbConn.Close();
        }

    }
}
